package br.ufrj.dcc.xml.database;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.URL;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import br.ufrj.dcc.xml.dynamicintervals.vo.Document;
import br.ufrj.dcc.xml.dynamicintervals.vo.Item;

public class ItemManager extends DatabaseManager {

	public ItemManager(DatabaseSettings settings) {
		super(settings);
	}

	public ItemManager() {
		super(DatabaseSettings.getInstance());
	}

	private PreparedStatement insertDocStatement;

	private PreparedStatement insertItemStatement;

	private PreparedStatement selectFullDocumentStatement;

	private PreparedStatement deleteDocStatement;

	private PreparedStatement selectFullNodeStatement;

	public void createTables() throws SQLException {
		Statement statement = createStatement();
		try {
			statement.getConnection().setAutoCommit(false);
			statement.execute(QueryConstants.createDocTable
					+ QueryConstants.createItemTable);

			URL functionParentURL = getClass().getResource("/sql/func_Parent.sql");
			URL functionAncestorURL = getClass().getResource("/sql/func_Ancestor.sql");
			
			String func_Parent = "";
			String func_anc = "";
			try {
				func_Parent = getSQLFromFile(functionParentURL);
				statement.execute(func_Parent);
				func_anc = getSQLFromFile(functionAncestorURL);
				statement.execute(func_anc);
			} catch (IOException e) {
				throw new SQLException("Erro ao ler os arquivos SQL");
			}
				

			statement.getConnection().commit();
			statement.getConnection().setAutoCommit(true);

			if (!QueryConstants.BENCHMARK)
				print(QueryConstants.createDocTable + "\n" +
						QueryConstants.createItemTable + "\n" +
						func_Parent + '\n' + func_anc);

		} catch (SQLException e) {
			try {
				statement.getConnection().rollback();
				throw e;
			} catch (SQLException ex) {
				throw ex;
			}
		}

	}

	private String getSQLFromFile(URL functionURL) throws IOException {
		InputStream in = functionURL.openStream();
		BufferedReader dis = new BufferedReader(new InputStreamReader(
				in));
		StringBuffer fBuf = new StringBuffer();

		String line;
		while ((line = dis.readLine()) != null) {
			fBuf.append(line + "\n");
		}
		String result = fBuf.toString();
		in.close();
		return result;
	}

	public synchronized int createDoc(String docName) throws SQLException {
		PreparedStatement statement = getInsertDocStatement();
		statement.setString(1, docName);
		statement.execute();
		Statement st = createStatement();
		ResultSet result = st.executeQuery(QueryConstants.selectHigherDocId);
		result.next();

		if (!QueryConstants.BENCHMARK)
			print("INSERT INTO documents" + " (nome)" + " VALUES (" + docName
					+ ")");

		return result.getInt(1);
	}

	public void insertItem(int docIndex, Item item) throws SQLException {
		PreparedStatement statement = getInsertItemStatement();
		statement.setInt(1, docIndex);
		statement.setInt(2, item.getL());
		statement.setInt(3, item.getR());
		statement.setInt(4, item.getType());
		statement.setString(5, item.getData());
		statement.execute();

		if (!QueryConstants.BENCHMARK)
			print("INSERT INTO items" + " (doc, l, r, type, data)"
					+ " VALUES (" + Integer.toString(item.getL()) + ", "
					+ Integer.toString(item.getR()) + ", "
					+ Integer.toString(item.getType()) + ", " + item.getData()
					+ ")");
	}

	public ResultSet selectFullDocument(int idXmlFile) throws SQLException {
		PreparedStatement statement = getSelectFullDocumentStatement();
		statement.setInt(1, idXmlFile);
		ResultSet rs = statement.executeQuery();

		if (!QueryConstants.BENCHMARK)
			print("SELECT data, l, r, type FROM items " + "WHERE doc = "
					+ idXmlFile + " order by l;");

		return rs;
	}

	public ResultSet selectFullNode(Item item, int idDoc) throws SQLException {
		PreparedStatement statement = getSelectFullNodeStatement();
		statement.setInt(1, idDoc);
		statement.setInt(2, item.getL());
		statement.setInt(3, item.getR());

		ResultSet rs = statement.executeQuery();

		if (!QueryConstants.BENCHMARK)
			print("SELECT data, l, r, type FROM items " + "WHERE doc = "
					+ idDoc + " l >= " + item.getL() + " r<= " + item.getR()
					+ " order by l;");

		return rs;
	}

	public ResultSet select(String query) throws SQLException {
		Statement st = createStatement();
		ResultSet rs = st.executeQuery(query);

		if (!QueryConstants.BENCHMARK)
			print(query);
		return rs;
	}

	public List<Document> selectDocuments() throws SQLException {
		List<Document> docList = new ArrayList<Document>();
		Statement statement = createStatement();
		ResultSet rs = statement.executeQuery(QueryConstants.selectDocs);

		if (!QueryConstants.BENCHMARK)
			print(QueryConstants.selectDocs);

		while (rs.next()) {
			Document doc = new Document(rs.getInt("id"), rs.getString("nome"));
			docList.add(doc);
		}
		return docList;
	}

	public void dropTables() throws SQLException {
		Statement statement = createStatement();
		// Item deve ser deletado primeiro pois n�o estou deletando a
		// constraint.
		// statement.execute(dropConstraints);
		try {
			statement.getConnection().setAutoCommit(false);
			statement.execute(QueryConstants.dropItemTable
					+ QueryConstants.dropDocTable);
			statement.getConnection().commit();
			statement.getConnection().setAutoCommit(true);

			if (!QueryConstants.BENCHMARK)
				print(QueryConstants.dropItemTable
						+ QueryConstants.dropDocTable);

		} catch (SQLException e) {
			try {
				statement.getConnection().rollback();
				throw e;
			} catch (SQLException ex) {
				throw ex;
			}
		}
	}

	public void deleteDoc(int id) throws SQLException {
		PreparedStatement statement = getDeleteDocStatement();
		statement.setInt(1, id);

		statement.execute();

		if (!QueryConstants.BENCHMARK)
			print(" DELETE TABLE documents WHERE id = " + id);

	}

	private PreparedStatement getDeleteDocStatement() throws SQLException {
		if (deleteDocStatement == null) {
			deleteDocStatement = createPreparedStatement(QueryConstants.deleteDoc);
		}
		return deleteDocStatement;
	}

	protected PreparedStatement getInsertDocStatement() throws SQLException {
		if (insertDocStatement == null) {
			insertDocStatement = createPreparedStatement(QueryConstants.insertDocSQL);
		}
		return insertDocStatement;
	}

	protected PreparedStatement getInsertItemStatement() throws SQLException {
		if (insertItemStatement == null) {
			insertItemStatement = createPreparedStatement(QueryConstants.insertItemSQL);
		}
		return insertItemStatement;
	}

	protected PreparedStatement getSelectFullDocumentStatement()
			throws SQLException {
		if (selectFullDocumentStatement == null) {
			selectFullDocumentStatement = createPreparedStatement(QueryConstants.selectFullDocumentSQL);
		}
		return selectFullDocumentStatement;
	}

	protected PreparedStatement getSelectFullNodeStatement()
			throws SQLException {
		if (selectFullNodeStatement == null) {
			selectFullNodeStatement = createPreparedStatement(QueryConstants.selectFullNode);
		}
		return selectFullNodeStatement;
	}

	public String fullNode(Item item) {

		return null;
	}

	/**
	 * Imprime as queries no panel de SQL..
	 * 
	 * @param outText
	 */
	/*
	 * Pro benchmark, desabilitamos cada chamada em vez de desabilitar aqui, pra
	 * evitar que a soma das strings que formam cada query atrapalhe no
	 * desempenho
	 */
	public void print(String outText) {
		outputText.append(outText + "\n");
	}
}
